INSERT OVERWRITE TABLE MID_addVip_CD 
SELECT EFFECTIVEDATE,COUNT(CARD_ID)AS COUNT 
FROM TBL_CARD_CD 
GROUP BY EFFECTIVEDATE;

INSERT OVERWRITE TABLE RES_addVip_CD 
select date,count,sum(count) over(order by date) 
from mid_addvip_CD;

INSERT OVERWRITE TABLE mid_vipChannel_cd SELECT RECORDTIME,
SUM(CASE WHEN MSGSOURCE='1' THEN 1 ELSE 0 END) AS unknown,
SUM(CASE WHEN MSGSOURCE='2' THEN 1 ELSE 0 END) AS PC,
SUM(CASE WHEN MSGSOURCE='3' THEN 1 ELSE 0 END) AS mobile,
SUM(CASE WHEN MSGSOURCE='4' THEN 1 ELSE 0 END) AS wechat,
SUM(CASE WHEN MSGSOURCE='5' THEN 1 ELSE 0 END) AS alipay
from TBL_CUSTOMER_CD GROUP BY RECORDTIME;

insert overwrite table res_vipChannel_cd select * from mid_vipChannel_cd where date is not null;

insert overwrite table mid_funnelSum_cd
select A.montha,B.sum
from
(select substr(date,1,7)as montha,max(date)as yuedi
from res_addvip_cd
group by substr(date,1,7))as A
join res_addvip_cd as B
on A.yuedi=B.date;

insert overwrite table res_vipLiveness_cd select A.MONTH,
SUM(CASE WHEN A.COUNT<'1' THEN 1 ELSE 0 END) AS zero,
SUM(CASE WHEN A.COUNT='1' THEN 1 ELSE 0 END) AS one,
SUM(CASE WHEN A.COUNT='2' THEN 1 ELSE 0 END) AS two,
SUM(CASE WHEN A.COUNT='3' THEN 1 ELSE 0 END) AS three,
SUM(CASE WHEN A.COUNT='4' THEN 1 ELSE 0 END) AS four,
SUM(CASE WHEN A.COUNT='5' THEN 1 ELSE 0 END) AS five,
SUM(CASE WHEN A.COUNT>'5' THEN 1 ELSE 0 END) AS overFive
from (SELECT SUBSTR(SENDTIME,1,7)AS MONTH,CARDUSERID,COUNT(CARDUSERID)AS COUNT FROM TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 GROUP BY CARDUSERID,SUBSTR(SENDTIME,1,7))A GROUP BY MONTH;

insert overwrite table res_vipLiveness_cd
select A.month,A.sum-B.one-B.two-B.three-B.four-B.five-B.overFive,B.one,B.two,B.three,B.four,B.five,B.overFive
FROM mid_funnelSum_cd as A
join  res_vipLiveness_cd as B
on A.month=B.month;

insert overwrite table res_funnel_cd
select C.month,C.sum,C.leastOnce,C.never,D.one+D.two+D.three+D.four+D.five+D.overFive
FROM
(select A.month,A.sum,B.leastOnce,(A.sum-B.leastOnce)as never
from mid_funnelSum_cd as A 
join mid_funnelLeastOnce_cd as B
on A.month=B.month)as C
JOIN res_vipLiveness_cd as D
on C.month=D.month;

insert overwrite table res_vipFirstExpend_cd
select Y.CARDUSER_ID,datediff(Z.MIN,Y.RECORDTIME)
FROM TBL_CARDUSER_CD AS Y 
JOIN (SELECT CARDUSERID,MIN(SENDTIME)AS MIN FROM TBL_ORDERDETAILS_CD GROUP BY CARDUSERID) AS Z 
ON Y.CARDUSER_ID=Z.CARDUSERID order by Y.CARDUSER_ID;

insert overwrite table res_vipGap_cd 
SELECT CARDUSERID,datediff(max,min)/count 
from (select CARDUSERID,COUNT(ORDERDETAILS_ID)AS count ,MAX(SENDTIME)AS max,MIN(SENDTIME)AS min 
FROM TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 GROUP BY CARDUSERID)AS a 
order by CARDUSERID;

insert overwrite table res_vipDealMonth_cd
select substr(SENDTIME,1,7)AS month,CARDUSERID,COUNT(CARDUSERID),SUM(AMOUNT)
FROM TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4
GROUP BY substr(SENDTIME,1,7),CARDUSERID
order by CARDUSERID;

insert overwrite table res_vipLastDeal_cd
select CARDUSERID,datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),MAX(SENDTIME))
FROM TBL_ORDERDETAILS_CD
GROUP BY CARDUSERID
order by CARDUSERID;

insert overwrite table res_recharge_cd
select CREATEDATE,count(AMOUNT) AS tradeNumber,sum(AMOUNT) as totalAmount 
from TBL_ORDER_CD where TRADEID > 0 group by CREATEDATE;

insert overwrite table mid_vipRechargeMonth_cd 
select SUBSTR(CREATEDATE,1,7)AS MONTH,CARDUSER_ID,count(CARDUSER_ID),SUM(AMOUNT)
FROM TBL_ORDER_CD where TRADEID > 0
group by SUBSTR(CREATEDATE,1,7),CARDUSER_ID order by MONTH;

insert overwrite table res_vipRechargeMonth_cd 
select * from mid_vipRechargeMonth_cd where carduser_id is not null;

insert overwrite table mid_vipEvaluation_cd
select to_date(Y.time),
if(Z.OLDUNIQUEID = '58010','58008',OLDUNIQUEID),
count(Y.evaluation_id),
SUM(Y.STAR),
SUM(Y.STAR2),
SUM(Y.STAR3),
SUM(Y.STAR4),
SUM(Y.STAR5)
FROM TBL_EVALUATION_CD AS Y 
join TBL_ORGANIZATION_CD AS Z 
ON Y.ORG_ID=Z.ID
GROUP BY to_date(Y.time),Z.OLDUNIQUEID;

INSERT OVERWRITE TABLE res_vipEvaluation_cd
select a.date,a.stationid,b.sum,a.dealnumber,if(a.star1total is null,0,star1total),if(a.star2total is null,0,star2total),if(a.star3total is null,0,star3total),if(a.star4total is null,0,star4total),if(a.star5total is null,0,star5total)
from mid_vipEvaluation_cd as a
join 
(select substr(minutes,1,10) as days,stationid,sum(oilnumber)as sum from res_oil_vip group by substr(minutes,1,10),stationid) as b
on a.date = b.days and a.stationid = b.stationid;

insert overwrite table res_vipEvaluationb_cd
select substr(A.TIME,1,10),if(D.OLDUNIQUEID = '58010','58008',OLDUNIQUEID),
B.PROBLEM_ID,C.PROBLEMTEXT,
sum(case when B.RESULT = '1' THEN 1 ELSE 0 END)AS yes,
sum(case when B.RESULT = '0' THEN 1 ELSE 0 END)AS no,
sum(case when B.RESULT = '2' THEN 1 ELSE 0 END)AS unknow
from TBL_EVALUATION_CD  AS A 
JOIN TBL_EVALUATIONRELPROBLEM_CD AS B
ON B.EVALUATION_ID = A.EVALUATION_ID
join TBL_EVALUATIONPROBLEM_CD AS  C 
ON C.ID = B.PROBLEM_ID
JOIN TBL_ORGANIZATION_CD AS D
ON D.ID = A.ORG_ID
group by substr(A.TIME,1,10),D.OLDUNIQUEID,B.PROBLEM_ID,C.PROBLEMTEXT;

insert OVERWRITE TABLE res_credits_cd
select A.RECORDTIME,A.getCredits,B.usedCredits
from
(select RECORDTIME,sum(abs(AMOUNT))/100 AS getCredits
from TBL_TRADE_CD where TRADE_TYPE='103'
group by RECORDTIME)as A
join
(SELECT RECORDTIME,SUM(abs(AMOUNT))/100 as usedCredits
FROM TBL_TRADE_CD where TRADE_TYPE='155' or TRADE_TYPE='158'
group by RECORDTIME)as B
on A.RECORDTIME=B.RECORDTIME;


insert overwrite table res_creditsRate_cd
SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd'),A.GET,B.USED,A.GET-B.USED
FROM
(SELECT SUM(abs(AMOUNT))/100 AS GET
FROM TBL_TRADE_CD WHERE TRADE_TYPE='103')AS A
full JOIN
(SELECT SUM(abs(AMOUNT))/100 AS USED
FROM TBL_TRADE_CD WHERE TRADE_TYPE='155' or TRADE_TYPE='158')AS B;

insert overwrite table mid_funnelSum_yearcd
select A.year,B.sum
from
(select substr(date,1,4)as year,max(date)as yuedi
from res_addVip_cd
group by substr(date,1,4))as A
join res_addVip_cd as B
on A.yuedi=B.date;

insert overwrite table res_vipLiveness_yearcd
select A.year,
SUM(CASE WHEN A.COUNT<'1' THEN 1 ELSE 0 END) AS zero,
SUM(CASE WHEN A.COUNT>='1' and  A.COUNT<='5' THEN 1 ELSE 0 END) AS one,
SUM(CASE WHEN A.COUNT>='6' and  A.COUNT<='10' THEN 1 ELSE 0 END) AS two,
SUM(CASE WHEN A.COUNT>='11' and  A.COUNT<='15' THEN 1 ELSE 0 END) AS three,
SUM(CASE WHEN A.COUNT>='16' and  A.COUNT<='20' THEN 1 ELSE 0 END) AS four,
SUM(CASE WHEN A.COUNT>='21' and  A.COUNT<='25' THEN 1 ELSE 0 END) AS five,
SUM(CASE WHEN A.COUNT>'26' THEN 1 ELSE 0 END) AS overfive
from 
(SELECT SUBSTR(SENDTIME,1,4)AS year,CARDUSERID,COUNT(CARDUSERID)AS COUNT 
FROM TBL_ORDERDETAILS_CD  WHERE ORDER_DEAL = 4 GROUP BY CARDUSERID,SUBSTR(SENDTIME,1,4))A 
GROUP BY year;

insert overwrite table res_vipLiveness_yearcd
select A.year,A.sum-B.one_five-B.six_ten-B.eleven_fifteen-B.sixteen_twenty-B.twentyone_twentyfive-B.overtwentysix,
B.one_five,B.six_ten,B.eleven_fifteen,B.sixteen_twenty,B.twentyone_twentyfive,B.overtwentysix
FROM 
mid_funnelSum_yearcd as A
join  res_vipLiveness_yearcd as B
on A.year=B.year;

insert overwrite table res_vipLiveness_stationcd
select A.MONTH,A.STATIONCODE,
SUM(CASE WHEN A.COUNT='1' THEN 1 ELSE 0 END) AS one,
SUM(CASE WHEN A.COUNT='2' THEN 1 ELSE 0 END) AS two,
SUM(CASE WHEN A.COUNT='3' THEN 1 ELSE 0 END) AS three,
SUM(CASE WHEN A.COUNT='4' THEN 1 ELSE 0 END) AS four,
SUM(CASE WHEN A.COUNT='5' THEN 1 ELSE 0 END) AS five,
SUM(CASE WHEN A.COUNT>'5' THEN 1 ELSE 0 END) AS overfive
from 
(SELECT SUBSTR(SENDTIME,1,7)AS MONTH,STATIONCODE,CARDUSERID,COUNT(CARDUSERID)AS COUNT 
FROM TBL_ORDERDETAILS_CD  WHERE ORDER_DEAL = 4 GROUP BY CARDUSERID,SUBSTR(SENDTIME,1,7),STATIONCODE)A 
GROUP BY MONTH,STATIONCODE;


insert overwrite table res_rechargeb_cd 
select CREATEDATE,
sum(case when OPENID = 'jdMember' then 1 else 0 end) as jdtradeNumber,
sum(case when OPENID = 'jdMember' then AMOUNT else 0 end) as jdtotalAmount,
sum(case when OPENID != 'jdMember' then 1 else 0 end) as wxtradeNumber,
sum(case when OPENID != 'jdMember' then AMOUNT else 0 end) as wxtotalAmount
from TBL_ORDER_CD where ORDER_STATUS = 2 group by CREATEDATE;

insert overwrite table res_vipEvaluation_all
select * from res_vipEvaluation 
union all 
select * from res_vipEvaluation_cd;

insert overwrite table res_vipEvaluationb_all
select * from res_vipEvaluationb 
union all 
select * from res_vipEvaluationb_cd;

insert overwrite table res_vipLiveness_stationall
select * from res_vipLiveness_stationbj
union all
select * from res_vipLiveness_stationcd;

insert overwrite table MID_EVALUATION_cd
select EVALUATION_ID,
sum(case when PROBLEM_ID = 100000 then RESULT else 0 end),
sum(case when PROBLEM_ID = 100001 THEN RESULT else 0 end),
sum(case when PROBLEM_ID = 100002 THEN RESULT else 0 end),
sum(case when PROBLEM_ID = 100020 THEN RESULT else 0 end),
sum(case when PROBLEM_ID = 100021 THEN RESULT else 0 end)
from TBL_EVALUATIONRELPROBLEM_CD 
group by EVALUATION_ID; 


insert overwrite table res_evaluation_cd
select if(B.OLDUNIQUEID = '58010','58008',OLDUNIQUEID ),
C.MOBILEPHONE,A.TIME,
D.hello,D.clean,D.sale,D.goodbye,D.toilet,
A.STAR4,A.STAR3,A.STAR,A.CONTENT
from TBL_EVALUATION_CD AS A 
join TBL_ORGANIZATION_CD AS B 
ON B.ID = A.ORG_ID
JOIN TBL_CUSTOMER_CD AS C
ON A.customer_id = C.customer_id 
JOIN MID_EVALUATION_CD AS D
ON D.EVALUATION_ID = A.EVALUATION_ID;


insert overwrite table res_evaluation_cd
select station_id,mobilePhone,evaluation_time,
case when hello = 0 then '否' 
     when hello = 1 then '是'
     when hello = 2 then '未填写'
else hello end,
case when clean = 0 then '否' 
     when clean = 1 then '是'
     when clean = 2 then '未填写'
else clean end,
case when sale = 0 then '否' 
     when sale = 1 then '是'
     when sale = 2 then '未填写'
else sale end,
case when goodbye = 0 then '否' 
     when goodbye = 1 then '是'
     when goodbye = 2 then '未填写'
else goodbye end,
case when toilet = 0 then '否' 
     when toilet = 1 then '是'
     when toilet = 2 then '未填写'
else toilet end,
STAR4,
star3,
star,
CONTENT
from res_evaluation_cd;

insert overwrite table res_evaluation
select * from res_evaluation
union all select * from res_evaluation_cd;
